package jp.co.mclnet.util;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import jp.co.mclnet.dao.JdbcDaoImpl;
import jp.co.mclnet.exception.NotFoundException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class KusanoCalc {

    public static void main(String[] args) {
        // JdbcDaoImpl dao = new JdbcDaoImpl("personal");
        // dao.setConnection(null);
        // LinkedHashMap dto = new LinkedHashMap<Object, Object>();
        // dto.put("personal_id", "1");
        // dto.put("personal_name", "namae");
        // dao.create(dto);

        String filePath = "deprecated/xls/speed.xls";
        List<String> table = getSheetNames(filePath);
        for (int i = 0; i < table.size(); i++) {
            List<Map<String, Object>> dto = columnValues(filePath, table.get(i));
            insertTable(table.get(i), dto);
        }

        // InputStream is = null;
        // try {
        // try {
        // is = new FileInputStream(filePath);
        // } catch (FileNotFoundException e) {
        // throw new IllegalArgumentException(e);
        // }
        // is = new BufferedInputStream(is);
        // List<String> columnNames = new ArrayList<String>();
        // List<Map<String, Object>> allColumnValues = new ArrayList<Map<String,
        // Object>>();
        //
        // POIFSFileSystem pfs = new POIFSFileSystem(is);
        // HSSFWorkbook book = new HSSFWorkbook(pfs);
        // for (int z = 0; z < book.getNumberOfSheets(); z++) {
        // HSSFSheet sheet = book.getSheetAt(z);
        //
        // int firstRowNum = sheet.getFirstRowNum();
        // int lastRowNum = sheet.getLastRowNum();
        //
        // for (int y = firstRowNum; y <= lastRowNum; y++) {
        // LinkedHashMap<String, Object> dto = new LinkedHashMap<String,
        // Object>();
        // HSSFRow row = (HSSFRow) sheet.getRow(y);
        // if (row == null) {
        // continue;
        // }
        // short firstCellNum = row.getFirstCellNum();
        // short lastCellNum = row.getLastCellNum();
        // for (short x = firstCellNum; x <= lastCellNum; x++) {
        // HSSFCell cell = (HSSFCell) row.getCell((int) x);
        //
        // if (cell == null) {
        // continue;
        // }
        // Object value = CalcUtils.getValue(cell);
        // // System.out.print(value);
        // // System.out.print("[");
        // // System.out.print(value.getClass().getName());
        // // System.out.println("]");
        // if (y == 0) {
        // columnNames.add((String) value);
        // } else {
        // dto.put(columnNames.get(x), value);
        // }
        // }
        // if (y != 0) {
        // allColumnValues.add(dto);
        // }
        // System.out.println(columnNames);
        // System.out.println(allColumnValues);
        // }
        //
        // String tableName = sheet.getSheetName();
        // List<Map> record = new ArrayList<Map>();
        // LinkedHashMap<Object, Object> dto = new LinkedHashMap<Object,
        // Object>();
        // for (int i = 0; allColumnValues.size() > i; i++) {
        // for (int j = 0; columnNames.size() > j; j++) {
        // Map<String, Object> map = (Map<String, Object>)
        // allColumnValues.get(i);
        // String aaa = columnNames.get(j);
        // aaa = Utils.toCamelCase(aaa);
        // dto.put(aaa, map.get(aaa));
        // }
        // record.add(dto);
        // }
        //
        // try {
        // Class.forName("com.mysql.jdbc.Driver");
        // } catch (ClassNotFoundException e) {
        // throw new IllegalArgumentException(e);
        // }
        // Connection conn = null;
        // try {
        // conn = DriverManager
        // .getConnection(
        // "jdbc:mysql://localhost:3306/saturday?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true",
        // "root", "");
        // JdbcDaoImpl dao = new JdbcDaoImpl(tableName);
        // dao.setConnection(conn);
        // dao.delete(new LinkedHashMap<String, Object>());
        // for(int i=0;i < record.size();i++){
        // dao.create((LinkedHashMap<String,Object>)record.get(i));
        // }
        // } catch (SQLException e) {
        // throw new IllegalArgumentException(e);
        // } finally {
        // if (conn != null) {
        // try {
        // conn.close();
        // conn = null;
        // } catch (SQLException e) {
        // throw new IllegalArgumentException(e);
        // }
        // }
        // }
        // }
        // } catch (IOException e) {
        // throw new IllegalArgumentException(e);
        // } finally {
        // if (is != null) {
        // try {
        // is.close();
        // } catch (IOException e) {
        // throw new IllegalStateException(e);
        // }
        // }
        // }
    }

    private static List<String> getSheetNames(String fileName) {
        InputStream is = null;
        List<String> list = new ArrayList<String>();
        try {
            try {
                is = new FileInputStream(fileName);
            } catch (FileNotFoundException e) {
                throw new IllegalArgumentException(e);
            }
            is = new BufferedInputStream(is);
            POIFSFileSystem pfs = new POIFSFileSystem(is);
            HSSFWorkbook book = new HSSFWorkbook(pfs);
            for (int z = 0; z < book.getNumberOfSheets(); z++) {
                HSSFSheet sheet = book.getSheetAt(z);
                list.add(sheet.getSheetName());
            }
        } catch (IOException e) {
            throw new IllegalArgumentException(e);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    throw new IllegalStateException(e);
                }
            }
        }
        return list;
    }

    private static List<Map<String, Object>> columnValues(String fileName,
            String tableName) {
        InputStream is = null;
        try {
            try {
                is = new FileInputStream(fileName);
            } catch (FileNotFoundException e) {
                throw new IllegalArgumentException(e);
            }
            is = new BufferedInputStream(is);

            POIFSFileSystem pfs = new POIFSFileSystem(is);
            HSSFWorkbook book = new HSSFWorkbook(pfs);
            for (int z = 0; z < book.getNumberOfSheets(); z++) {
                HSSFSheet sheet = book.getSheetAt(z);
                if (tableName != sheet.getSheetName()) {
                    continue;
                }
                List<Map<String, Object>> allColumnValues = new ArrayList<Map<String, Object>>();
                int firstRowNum = sheet.getFirstRowNum();
                int lastRowNum = sheet.getLastRowNum();

                for (int y = firstRowNum; y <= lastRowNum; y++) {
                    LinkedHashMap<String, Object> dto = new LinkedHashMap<String, Object>();
                    HSSFRow row = (HSSFRow) sheet.getRow(y);
                    if (row == null) {
                        continue;
                    }
                    short firstCellNum = row.getFirstCellNum();
                    short lastCellNum = row.getLastCellNum();
                    for (short x = firstCellNum; x <= lastCellNum; x++) {
                        HSSFCell cell = (HSSFCell) row.getCell((int) x);

                        if (cell == null) {
                            continue;
                        }
                        Object value = CalcUtils.getValue(cell);
                    }
                    if (y != 0) {
                        allColumnValues.add(dto);
                    }
                    System.out.println(allColumnValues);
                    return allColumnValues;
                }
            }
        } catch (IOException e) {
            throw new IllegalArgumentException(e);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    throw new IllegalStateException(e);
                }
            }
        }
        return null;
    }

    private static void insertTable(String tableName,
            List<Map<String, Object>> dto) {
        Connection conn = null;
        try {
            conn = DriverManager
                    .getConnection(
                            "jdbc:mysql://localhost:3306/saturday?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true",
                            "root", "");
            JdbcDaoImpl dao = new JdbcDaoImpl(tableName);
            dao.setConnection(conn);
            try {
                dao.delete(new LinkedHashMap<String, Object>());
            } catch (NotFoundException e) {
            }
            for (int i = 0; i < dto.size(); i++) {
                //TODO key,valの値でcreateする
                dao.create((LinkedHashMap<String, Object>) dto.get(i));
            }
        } catch (SQLException e) {
            throw new IllegalArgumentException(e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException e) {
                    throw new IllegalArgumentException(e);
                }
            }
        }
    }
}
